#!/usr/bin/perl
use lib "$ENV{MYSQL_DRAW}/lib";
use MyTools;
use strict;
use Data::Dumper;
use Getopt::Long;
use DBI;

my ($subject_name, $flowcell_name, $readgroup_name, $virtual_lane, $debug);
my @argv = @ARGV;
&GetOptions("debug!"=> \$debug,
            "subject_name=s" => \$subject_name,
            "flowcell_name=s" => \$flowcell_name,
            "readgroup_name=s" => \$readgroup_name,
            "virtual_lane=s" => \$virtual_lane);

if ((!$subject_name) || (!$flowcell_name)) {
	die "usage: $0 --subject_name PSP7020 --flowcell_name Project_48442 [--debug]\n(use --debug to preview what would be inserted into mysql table)\n";
}
$readgroup_name = "${flowcell_name}${subject_name}" if (!$readgroup_name);
$virtual_lane = "Sample_${subject_name}" if (!$virtual_lane);

my $table = `basename $0 .pl | sed s/insert//`;
chomp($table);
printf STDERR "Inserting $readgroup_name into table $table\n";
if ($debug) {
	printf "Debug mode. The following are aguments\n";
}
else {
	my $newid=driver();
	printf STDERR "${subject_name}_${flowcell_name} %s\n\n", ($newid) ? "inserted into $table with RGID=$newid" : "already in $table\n\n";
}

sub driver {
	my $dbh = DBI->connect($ENV{dbi1}, $ENV{dbi2}, $ENV{dbi3}) or die "Couldn't connect to database: " . DBI->errstr;;
	my $dr_id = MyTools::fetchrow1col1_arrayref($dbh, "select DRID from DrawRun as dr join Flowcell as fc on dr.FlowcellID=fc.FID where ", {'fc.FlowcellName' => $flowcell_name});
	# it's possible that one subject has multiple samples (by different captures for instance)
	my $sm_records = MyTools::my_fetchall_arrayref($dbh, "select sm.* from Sample as sm join Subject as sb on sm.SubjectID=sb.SBID where ", {'sb.SubjectName' => $subject_name});
	my $sm_id = $sm_records->[0][0];
	my $record_num = scalar @{$sm_records};
	if ($record_num > 1) {
		printf STDERR "More than one sample found for Subject $subject_name\n";
		for (my $i=0; $i<$record_num; $i++) {
			printf STDERR "%s\n", join("\t", @{$sm_records->[$i]});
		}
		$sm_id = $sm_records->[$record_num-1][0];
		printf STDERR "SampleID $sm_id would be used for this new ReadGroup record. Update this record's SampleID if another SMID should be used.\n";
	}
	elsif ($record_num == 1) {
		printf STDERR "SMID=$sm_id of table Sample will be linked to $readgroup_name\n";	
	}
	else {
		printf STDERR "No record in table Sample links to Subject $subject_name\n";
	}
	my $record_present = MyTools::fetchrow1col1_arrayref($dbh, "select RGID from ReadGroup where ", {'SampleID' => $sm_id, 'DrawRunID' => $dr_id});
	if (defined($record_present)) {
		printf STDERR "Existing RGID=%d ", $record_present;
		return 0;
	}
	my $sth = $dbh->prepare("INSERT INTO $table (SampleID,DrawRunID,ReadGroupName,VirtualLane) VALUES (?,?,?,?)") or die "Couldn't prepare statement: " . $dbh->errstr;
	$sth->execute($sm_id, $dr_id, $readgroup_name, $virtual_lane) or die "Couldn't execute statement: " . $sth->errstr;
	return MyTools::fetchrow1col1_arrayref($dbh, "select RGID from $table where ", {'SampleID' => $sm_id, 'DrawRunID' => $dr_id, 'ReadGroupName' => $readgroup_name});
}
